Need help with visual and a dax

[Migsmix]

Hello my friends,

I need help with some visuals i'm trying to make. Currently i have a report in excel but now that has a lot of data it's hard to use it because it lags a lot. In the attached file you can see how my report works. The PBI should look like the tab that says "Final in PBI". It has all the formulas. I was trying some Count Dax but it doesn't work. Here's my file 😀 File example 

Thanks!!

[MFelix · Accepted solution]

Hi @Migsmix ,

For this you need to make the following steps:

• Create a table with the cluster, measures and a total similar to the bottom one:

• Add the following measures to your model:

Distribution = COUNT(Table1[Inventory (Q)])

Number of Stores = CALCULATE(DISTINCTCOUNT(Table1[Store Name]),ALLSELECTED(Table1   ), VALUES(Table1[Cluster]))

Percentage = Divide( [Distribution] , [Number of Stores])

TBS =
VAR TotalStores =
    SUMX (
        SUMMARIZE (
            CALCULATETABLE ( Table1, ALLSELECTED ( Table1 ) ),
            Table1[Cluster],
            "Stores", [Number of Stores]
        ),
        [Stores]
    )
RETURN
    IF (
        ISINSCOPE ( Table1[Cluster] ),
        BLANK (),
        DIVIDE ( [Distribution], TotalStores )
    )

VAlues = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( Split[Clusters] ) <> "Total"
        && SELECTEDVALUE ( Split[Measure] ) = "Distribution", CALCULATE ( [Distribution], Table1[Cluster] IN VALUES ( Split[Clusters] ) ),
    SELECTEDVALUE ( Split[Clusters] ) <> "Total"
        && SELECTEDVALUE ( Split[Measure] ) = "Number of Stores", CALCULATE ( [Number of Stores], Table1[Cluster] IN VALUES ( Split[Clusters] ) ),
    SELECTEDVALUE ( Split[Clusters] ) <> "Total"
        && SELECTEDVALUE ( Split[Measure] ) = "Percentage",
        FORMAT (
            CALCULATE ( [Percentage], Table1[Cluster] IN VALUES ( Split[Clusters] ) ),
            "0%"
        ),
    SELECTEDVALUE ( Split[Clusters] ) = "Total"
        && SELECTEDVALUE ( Split[Measure] ) = "TBS", FORMAT ( [TBS], "0%" )
)

• Setup you matrix in the following way:
  • Rows: 
    • Code2
    • Name
  • Columns:
    • Cluster (from the new table)
    • Measure(from the new table)
  • Values
    • Values measure
  • Turn of steped layout
  • Drill down on the columns and rows until the last level
  • turn off totals

Result below and in attach PBIX file:

[MFelix · Accepted solution]

Hi @Migsmix ,

For this you need to make the following steps:

• Create a table with the cluster, measures and a total similar to the bottom one:

• Add the following measures to your model:

Distribution = COUNT(Table1[Inventory (Q)])

Number of Stores = CALCULATE(DISTINCTCOUNT(Table1[Store Name]),ALLSELECTED(Table1   ), VALUES(Table1[Cluster]))

Percentage = Divide( [Distribution] , [Number of Stores])

TBS =
VAR TotalStores =
    SUMX (
        SUMMARIZE (
            CALCULATETABLE ( Table1, ALLSELECTED ( Table1 ) ),
            Table1[Cluster],
            "Stores", [Number of Stores]
        ),
        [Stores]
    )
RETURN
    IF (
        ISINSCOPE ( Table1[Cluster] ),
        BLANK (),
        DIVIDE ( [Distribution], TotalStores )
    )

VAlues = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( Split[Clusters] ) <> "Total"
        && SELECTEDVALUE ( Split[Measure] ) = "Distribution", CALCULATE ( [Distribution], Table1[Cluster] IN VALUES ( Split[Clusters] ) ),
    SELECTEDVALUE ( Split[Clusters] ) <> "Total"
        && SELECTEDVALUE ( Split[Measure] ) = "Number of Stores", CALCULATE ( [Number of Stores], Table1[Cluster] IN VALUES ( Split[Clusters] ) ),
    SELECTEDVALUE ( Split[Clusters] ) <> "Total"
        && SELECTEDVALUE ( Split[Measure] ) = "Percentage",
        FORMAT (
            CALCULATE ( [Percentage], Table1[Cluster] IN VALUES ( Split[Clusters] ) ),
            "0%"
        ),
    SELECTEDVALUE ( Split[Clusters] ) = "Total"
        && SELECTEDVALUE ( Split[Measure] ) = "TBS", FORMAT ( [TBS], "0%" )
)

• Setup you matrix in the following way:
  • Rows: 
    • Code2
    • Name
  • Columns:
    • Cluster (from the new table)
    • Measure(from the new table)
  • Values
    • Values measure
  • Turn of steped layout
  • Drill down on the columns and rows until the last level
  • turn off totals

Result below and in attach PBIX file:

[Migsmix]

Thank you @MFelix ! works perfect. Is there a way to add color coding to the values? for example if the percentage is 100% Green, >=50% <100% Yellow, <50% Red?

[MFelix · Accepted solution]

Add the following measure:

VAlues Colouring =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( Split[Clusters] ) <> "Total"
        && SELECTEDVALUE ( Split[Measure] ) = "Percentage",
        CALCULATE (
            SWITCH (
                TRUE (),
                [Percentage] < 0.5, "Red",
                [Percentage] < 1, "Yellow",
                "Green"
            ),
            Table1[Cluster] IN VALUES ( Split[Clusters] )
        )
)

[Migsmix]

Thanks @MFelix . Do you know why the Total TBS is not colouring? 

[MFelix]

TBS is a different measure you need to add the conditional formating especially to that measure.