Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!!
Solved! Go to Solution.
Hi @Migsmix ,
For this you need to make the following steps:
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%" )
)
Result below and in attach PBIX file:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsAdd 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] )
)
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Migsmix ,
For this you need to make the following steps:
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%" )
)
Result below and in attach PBIX file:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank 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?
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] )
)
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsTBS is a different measure you need to add the conditional formating especially to that measure.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português