Warm tip: This article is reproduced from serverfault.com, please click
dax powerbi

DAX

发布于 2020-11-25 15:08:00

So, I have the following tables in my Power BI :

Sales : Date | ID_Client | ID_Product | Amount
Client : ID_Client | Name_Client

I would like to get the number of unique BIG clients in any given month. I therefore use the following formula (which I then put in a column in a table with months in rows):

# BIG Clients =
VAR threshold = 10000
RETURN
     (
        CALCULATE(
            DISTINCTCOUNT( Sales[ID_Client] ),
            FILTER(
                SUMMARIZE(
                    Sales,
                    Sales[ID_Client],
                    "Sales", SUM( Sales[Amount] )
                ),
                [Sales] >= threshold
            )
        )
    )

QUESTION IS : how can I get the list of those BIG clients for any given month? Let's say I click on the November number of big clients in my table, could another table nearby display the list of those clients ?

Thanks in advance for your kind help, I've been trying for a while :)

Questioner
Carto_
Viewed
0
分享
sergiom 2020-11-29 18:05:03

I assume that you have a table of clients with the Name column with a one to many relationship with the Sales table and that you do not have duplicate client names. Then you may create a [BIG Sales] measure to be used in a table or matrix visual with client names on the rows.

since [BIG Sales] evaluates to BLANK() for clients with less that threshold sales, they are automatically filtered out from the visual

BIG Sales =
VAR threshold = 10000
VAR BigCustomers =
    FILTER(
        ADDCOLUMNS(
            VALUES( Clients[Name] ),
            "Sales", SUM( Sales[Amount] )
        ),
        [Sales] >= threshold
    )
RETURN
    SUMX(
        BigCustomers,
        [Sales]
    )

热门github

1
A multi-platform library for OpenGL, OpenGL ES, Vulkan, window and input
2
Dev tool that writes scalable apps from scratch while the developer oversees the implementation
3
shadcn/ui, but for Svelte. ✨
4
The Python Risk Identification Tool for generative AI (PyRIT) is an open access automation framework to empower security professionals and machine learning engineers to proactively find risks in their generative AI systems.
5
Performance-portable, length-agnostic SIMD with runtime dispatch
6
ZK Credo
7
OpenCodeInterpreter: Integrating Code Generation with Execution and Refinement
8
Joplin - the secure note taking and to-do app with synchronisation capabilities for Windows, macOS, Linux, Android and iOS.
9
Mamba is a new state space model architecture showing promising performance on information-dense data such as language modeling, where previous subquadratic models fall short of Transformers. It is based on the line of progress on structured state space models, with an efficient hardware-aware design and implementation in the spirit of FlashAttention.
10
This repository contains System Design resources which are useful while preparing for interviews and learning Distributed Systems
11
Curso para aprender el lenguaje de programación Python desde cero y para principiantes. 75 clases, 37 horas en vídeo, código, proyectos y grupo de chat. Fundamentos, frontend, backend, testing, IA...
12
🎓 Path to a free self-taught education in Computer Science!
13
1️⃣🐝🏎️ The One Billion Row Challenge -- A fun exploration of how quickly 1B rows from a text file can be aggregated with Java
14
A collective list of free APIs
15
📚 Freely available programming books